package dao;

import pojo.Department;
import utils.JDBCUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDAOImpl {

    private static final String TABLE_NAME = "Department";

    public static void add(Department department) throws SQLException {
        String sql = "INSERT INTO " + TABLE_NAME + " (department_id, department_name, company_id) VALUES (?, ?, ?)";
        JDBCUtils.execute(sql, null, department.getDepartmentName(), department.getCompanyID());
    }

    public static void delete(int departmentId) throws SQLException {
        String sql = "DELETE FROM " + TABLE_NAME + " WHERE department_id = ?";
        JDBCUtils.execute(sql, departmentId);
    }

    public static void update(Department department) throws SQLException {
        String sql = "UPDATE " + TABLE_NAME + " SET department_name = ?, company_id = ? WHERE department_id = ?";
        JDBCUtils.execute(sql, department.getDepartmentName(), department.getCompanyID(), department.getDepartmentID());
    }

    public static Department getById(int departmentId) throws SQLException {
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE department_id = ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, departmentId);
        if (resultSet.next()) {
            return createDepartmentFromResultSet(resultSet);
        }
        return null;
    }

    public static List<Department> getAll() throws SQLException {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        while (resultSet.next()) {
            Department department = createDepartmentFromResultSet(resultSet);
            departments.add(department);
        }
        return departments;
    }

    public static int getCount() throws SQLException {
        String sql = "SELECT COUNT(*) AS count FROM " + TABLE_NAME;
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql);
        if (resultSet.next()) {
            return resultSet.getInt("count");
        }
        return 0;
    }

    public static List<Department> getByPage(int start, int pageSize) throws SQLException {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, start, pageSize);
        while (resultSet.next()) {
            Department department = createDepartmentFromResultSet(resultSet);
            departments.add(department);
        }
        return departments;
    }

    public static List<Department> findByKeyword(String keyword) throws SQLException {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE department_name LIKE ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%");
        while (resultSet.next()) {
            Department department = createDepartmentFromResultSet(resultSet);
            departments.add(department);
        }
        return departments;
    }

    public static List<Department> findByPageAndKeyword(int start, int pageSize, String keyword) throws SQLException {
        List<Department> departments = new ArrayList<>();
        String sql = "SELECT * FROM " + TABLE_NAME + " WHERE department_name LIKE ? LIMIT ?, ?";
        ResultSet resultSet = (ResultSet) JDBCUtils.execute(sql, "%" + keyword + "%", start, pageSize);
        while (resultSet.next()) {
            Department department = createDepartmentFromResultSet(resultSet);
            departments.add(department);
        }
        return departments;
    }

    private static Department createDepartmentFromResultSet(ResultSet resultSet) throws SQLException {
        int departmentId = resultSet.getInt("department_id");
        String departmentName = resultSet.getString("department_name");
        int companyId = resultSet.getInt("company_id");
        return new Department(departmentId, departmentName, companyId);
    }
}
